
with tmp_network as (
    select
        case when network_type = 3 then 1
             when network_type = 4 then 2
             when network_type = 5 then 3
             when network_type = 6 then 4
             when network_type = 2 then 5
             when network_type = 1 then 6
             else 0
        end as network_type
        ,code
        ,name as network_name
        ,agent_code
        ,agent_name
        ,fran_code as franchisee_code
        ,fran_name as franchisee_name
        ,center_code as transcenter_code
        ,center_name as transcenter_name
    from jms_dim.dim_network_whole_massage
)

insert overwrite table jms_dwd.dwd_tab_barscan_sitearrival_base_dt partition (dt)
select
tmp_barscan.recordid                 as record_id
,tmp_barscan.billcode                as waybill_no
,tmp_barscan.listcode                as list_code
,tmp_barscan.subbillcode             as subbill_code
,tmp_barscan.transfercode            as shipment_no
,tmp_barscan.packagecode             as package_code
,tmp_barscan.scantype                as scan_type
,tmp_barscan.nextstation             as pre_station
,tmp_barscan.destination             as destination
,tmp_barscan.scanuser                as scan_user
,tmp_barscan.inputsite               as scan_site
,tmp_barscan.scantime                as scan_time
,tmp_barscan.inputtime               as input_time
,tmp_barscan.operatedate             as operate_date
,tmp_barscan.send_deliver_user       as send_deliver_user
,tmp_barscan.sendcustomer            as send_customer
,tmp_barscan.sendsite                as send_site
,tmp_barscan.destsite                as dest_site
,tmp_barscan.transfercenter          as transfer_center
,tmp_barscan.pcs                     as pcs
,tmp_barscan.weight                  as weight
,tmp_barscan.goodstype               as goods_type
,tmp_barscan.expresstype             as transport_type
,tmp_barscan.shifts                  as shifts
,tmp_barscan.transfer_deliver_fee    as transfer_deliver_fee
,tmp_barscan.networkfee              as network_manage_fee
,tmp_barscan.otherfee                as other_fee
,tmp_barscan.checkflag               as check_flag
,tmp_barscan.accountflag             as co_operate_src
,tmp_barscan.receiptflag             as receipt_flag
,tmp_barscan.returnflag              as return_flag
,tmp_barscan.elescaleflag            as ele_scale_flag
,tmp_barscan.unrecordflag            as weight_type
,tmp_barscan.writebackflag           as write_back_flag
,tmp_barscan.lockflag                as auto_produce_type
,tmp_barscan.remark                  as width
,tmp_barscan.modifyuser              as station_num
,tmp_barscan.modifytime              as modifytime
,tmp_barscan.mobile                  as `length`
,tmp_barscan.pistolid                as pistol_id
,tmp_barscan.remark1                 as height
,tmp_barscan.remark2                 as flight_num
,tmp_barscan.scansitecode            as scan_site_code
,tmp_barscan.uploadtime              as upload_time
,tmp_barscan.remark3                 as len_wid_hei_product
,tmp_barscan.scanusercode            as scan_user_code
,tmp_barscan.send_deliver_usercode   as send_deliver_user_code
,tmp_barscan.nextstationcode         as pre_station_code
,tmp_barscan.source                  as source
,tmp_barscan.remark5                 as remark5
,tmp_barscan.remark6                 as remark6
,tmp_barscan.remark4                 as list_code_2
,tmp_barscan.scansiteid              as scan_site_id
,tmp_barscan.nextstationid           as pre_station_id
,tmp_barscan.scanuserid              as scan_user_id
,tmp_barscan.send_deliver_userid     as send_deliver_user_id
     , tmp_network.network_type          as network_type
     , tmp_network.agent_code            as site_agent_code
     , tmp_network.agent_name            as site_agent_name
     , tmp_network.franchisee_code       as site_franchisee_code
     , tmp_network.franchisee_name       as site_franchisee_name
     , tmp_network.transcenter_code      as site_transcenter_code
     , tmp_network.transcenter_name      as site_transcenter_name
     , tmp_network_pre.network_type     as pre_network_type
     , case
           when (length(tmp_barscan.billcode) = 13) then 'GB'
           when (length(tmp_barscan.billcode) = 15 and tmp_barscan.billcode like "55%") then 'BS'
           when (length(tmp_barscan.billcode) = 12 and (tmp_barscan.billcode like "40%" or billcode like "42%"))
               then 'BSB'
           when (length(tmp_barscan.billcode) = 15 and tmp_barscan.billcode like "JT%") then 'JT'
           when (length(tmp_barscan.billcode) = 13 and tmp_barscan.billcode like "B%") then 'JTB'
           else "Others"
    end                                  as waybill_source
  --   , 'scantype'    as scantype_id
,'{{ execution_date | cst_ds }}' as dt
from jms_ods.tab_barscan_sitearrival tmp_barscan
  left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
         left join tmp_network tmp_network_pre on tmp_barscan.nextstationcode = tmp_network_pre.code
where tmp_barscan.dt = '{{ execution_date | cst_ds }}';
